package cn.goktech.utils;

import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

public class JDBCUtil {
	/*// 驱动名字
		public static final String DRIVER_CLASS_NAME = "com.mysql.jdbc.Driver";
		// 连接地址
		public static final String URL = "jdbc:mysql://localhost:3306/db?useUnicode=true&characterEncoding=UTF-8";
		// 用户名
		public static final String USERNAME = "root";
		// 密码
		public static final String PASSWORD = "123";

		// 构造方法私有化
		public JDBCUtil() {
			// TODO Auto-generated constructor stub
		}*/

		/**
		 * 连接数据库
		 * 
		 * @return
		 */
		public static Connection getConnection() {
			Connection connection = null;
			/*try {
				Class.forName(DRIVER_CLASS_NAME);
				connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
				return connection;
			} catch (Exception e) {
				e.printStackTrace();
			}*/
			Context context = null;
			DataSource ds = null;
			//读取上下文
			try {
				context = new InitialContext();
				ds = (DataSource) context.lookup("java:comp/env/jdbc/myBlog");
				connection = ds.getConnection();
				return connection;
			} catch (NamingException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			return null;
		}
		/**
		 * 关闭资源
		 */
		public static void close(Connection conn, Statement st, ResultSet rs) {
			try {
				if (rs != null) { 
					rs.close();
					rs = null;// 资源回收
				}
				if (st != null) {

					st.close();
					st = null;
				}
				if (conn != null) {
					conn.close();
					conn = null;
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}

		/**
		 * 查询list
		 * 
		 * @param sql
		 * @param args
		 * @return
		 */
		public static List<Map<String, Object>> queryForList(String sql, Object... args) {
			Connection connection = null;
			PreparedStatement ps = null;
			ResultSet rs = null;
			try {
				connection = getConnection();
				ps = connection.prepareStatement(sql);
				// 设置参数
				for (int i = 0; args != null && i < args.length; i++) {
					ps.setObject(i + 1, args[i]);
				}
				// 执行sql语句
				rs = ps.executeQuery();
				// 获取元数据
				ResultSetMetaData rsmd = rs.getMetaData();
				// 获取列数
				int count = rsmd.getColumnCount();

				List<Map<String, Object>> list = new ArrayList<>();
				// 遍历结果集
				while (rs.next()) {
					Map<String, Object> map = new HashMap<>();
					for (int i = 0; i < count; i++) {// 遍历列
						String key = rsmd.getColumnLabel(i + 1);// getColumnLabel 从1开始
						Object value = rs.getObject(key);
						map.put(key, value);
					}
					list.add(map);
				}
				// 判断list
				if (list != null && list.size() > 0) {
					return list;
				}
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				close(connection, ps, rs);
			}

			return null;
		}

		public static Map<String, Object> queryForMap(String sql, Object... args) {
			List<Map<String, Object>> list = queryForList(sql, args);
			// 判断list是否为空
			if (list != null && list.size() > 0) {
				return list.get(0);
			}
			return null;
		}

		/**
		 * 增删改的操作 args：动态参数可以当成数组来使用
		 * 
		 * @param sql
		 * @param args
		 * @return
		 */
		public static int update(String sql, Object... args) {
			
			Connection connection = null;
			PreparedStatement ps = null;
			try {
				connection = getConnection();
				ps = connection.prepareStatement(sql);
				// 设置参数
				for (int i = 0; args != null && i < args.length; i++) {
					ps.setObject(i + 1, args[i]);
				}
				// 执行SQL语句
				return ps.executeUpdate();
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				close(connection, ps, null);
			}
			return 0;
		}

		/**
		 * 查询list<T>
		 * 
		 * @param sql
		 * @param c
		 * @param args
		 * @return
		 */
		public static <T> List<T> query(String sql, Class<T> c, Object... args) {
			Connection connection = null;
			PreparedStatement ps = null;
			ResultSet rs = null;
			try {
				connection = getConnection();
				ps = connection.prepareStatement(sql);
				//设置参数
				for (int i = 0;args!=null&& i < args.length; i++) {
					ps.setObject(i+1, args[i]);
				}
				//执行sql语句
				rs = ps.executeQuery();
				//通过反射获取获取类的所有属性(字段)
				Field[] fields = c.getDeclaredFields();
				List<T> list = new ArrayList<>();
				//获取元数据
				ResultSetMetaData rsmd = rs.getMetaData();
				//遍历结果集
				while(rs.next()){
					//根据类实例化对象
					T o = c.newInstance();//实例化对象
					//对象的属性赋值需要调用set方法
					//遍历所有字段
					for (int i = 0; i < fields.length; i++) {
						//获取到每个字段
						Field field = fields[i];
						//获取字段名
						String fieldName = field.getName();
						//获取字段类型
						Class<?> fieldType = field.getType();
						//获取set方法名  name--->set方法      setName();
						String methodName = "set"+(fieldName.charAt(0)+"").toUpperCase()+fieldName.substring(1);
						Method method = c.getMethod(methodName, fieldType);
						//判断是否在结果集中存在
						if (isExist(rsmd, fieldName)) {
							//根据类的属性名字获取字段的值
							Object value = rs.getObject(fieldName);
							if (value!= null) {
								method.invoke(o, value);
							}
						}
						
					}
					list.add(o);
				}
					if (list!=null||list.size()>0) {
						return list;
					}
				
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				close(connection, ps, rs);
			}
			
			return null;
		}

		/**
		 * 查询单个集合
		 * queryForObject("select...",User.class,"")
		 * 登陆 
		 * @param sql
		 * @param c
		 * @param args
		 * @return
		 */
		public static <T> T queryForObject(String sql, Class<T> c, Object... args) {
			List<T> list = query(sql, c, args);
			if (list != null && list.size() > 0) {
				return list.get(0);
			}
			return null;
		}

		/**
		 * 判断是否存在结果集中
		 */
		public static boolean isExist(ResultSetMetaData rsmd, String fieldName) {
			try {
				//获取列数
				int count = rsmd.getColumnCount();
				for (int i = 0; i < count; i++) {
					//获取每列的名字
					String colunName = rsmd.getColumnLabel(i+1);
					if (colunName.equals(fieldName)) {
						return true;
					}
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			return false;
		}
		
		public static int queryForInt(String sql, Object... args) {
			Connection connection = null;
			PreparedStatement ps = null;
			ResultSet rs = null;
			try {
				connection = getConnection();
				ps = connection.prepareStatement(sql);
				// 设置参数
				for (int i = 0; args != null && i < args.length; i++) {
					ps.setObject(i+1, args[i]);
				}
				// 执行sql语句
				rs = ps.executeQuery();
				//遍历结果集
				while(rs.next()){
					return rs.getInt(1);
				}
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				close(connection, ps, rs);
			}
			return 0;
		}

	
}
